﻿using System;
using System.Data;

namespace WebApi.DAL
{
    public class SqlHelper
    {
            string connectionString = "Data Source=139.129.30.78;Initial Catalog=SportMeet;Persist Security Info=True;User ID=sa;Password=123456";
            SqlConnection conn;
            private static SqlHelper insance;
            /// <summary>
            /// 实例化对象
            /// </summary>
            /// <returns></returns>
            public static SqlHelper _init()
            {
                if (insance == null)
                {
                    insance = new SqlHelper();
                }
                return insance;
            }


            /// <summary>
            /// 执行sql语句返回datatable
            /// </summary>
            /// <param name="sql"></param>
            /// <returns></returns>
            public DataTable doSelect(string sql)
            {
                using (conn = new SqlConnection(connectionString))
                {
                    try
                    {
                        conn.Open();
                        SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                        DataSet ds = new DataSet();
                        da.Fill(ds);
                        return ds.Tables[0];
                    }
                    catch (Exception ex)
                    {
                        conn.Close();
                        return null;
                    }
                }
            }

            public DataRow getRowObj(string sql)
            {
                using (conn = new SqlConnection(connectionString))
                {
                    try
                    {
                        conn.Open();
                        SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                        DataSet ds = new DataSet();
                        da.Fill(ds);
                        return ds.Tables[0].Rows[0];
                    }
                    catch (Exception ex)
                    {
                        conn.Close();
                        return null;
                    }
                }
            }

            /// <summary>
            /// 执行sql语句返回第一行第一列查询结果
            /// </summary>
            /// <param name="sql"></param>
            /// <returns></returns>
            public string ReturnStr(string sql)
            {
                using (conn = new SqlConnection(connectionString))
                {
                    try
                    {
                        conn.Open();
                        SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                        DataSet ds = new DataSet();
                        da.Fill(ds);
                        return ds.Tables[0].Rows[0][0].ToString();
                    }
                    catch (Exception ex)
                    {
                        conn.Close();
                        return null;
                    }
                }
            }

            /// <summary>
            /// 执行查询，获取结果集的count总数
            /// </summary>
            /// <returns></returns>
            public int getCount(string sql)
            {
                using (conn = new SqlConnection(connectionString))
                {
                    conn.Open();
                    SqlCommand command = new SqlCommand(sql, conn);
                    try
                    {
                        command.CommandText = sql;
                        command.CommandType = CommandType.Text;
                        int count = (int)command.ExecuteScalar();
                        return count;
                    }
                    catch (Exception exception)
                    {
                        conn.Close();
                        return 0;
                    }
                }
            }
            /// <summary>
            /// 执行sql返回结果
            /// </summary>
            /// <param name="sql"></param>
            /// <returns>成功 true 失败 false</returns>
            public bool ExcuteSql(string sql)
            {
                using (conn = new SqlConnection(connectionString))
                {
                    SqlCommand com = new SqlCommand(sql, conn);
                    try
                    {
                        conn.Open();

                        int influence = com.ExecuteNonQuery();

                        if (influence > 0)
                        {
                            return true;
                        }
                        else
                        {
                            return false;
                        }
                    }
                    catch (Exception exception)
                    {
                        conn.Close();
                        return false;
                    }
                }
            }
        }
}
